﻿#define _CRT_SECURE_NO_DEPRECATE
#include <stdio.h>
#include <stdlib.h>
#include <conio.h>
#include <windows.h> //包含了其它window头文件,为mmysql.h服务，且必须声明在mysql.h前面
#include <mysql.h>   //mysql数据库头文件
#pragma comment(lib, "libmysql.lib")

/* 定义 SQL 处理中要用到的变量 */
MYSQL mysql;          //定义mysql对象
MYSQL_RES* result;    //定义结果集变量
MYSQL_ROW row;        //定义行变量

/* 定义记录当前用户信息的变量 */
char realName[128];   //当前登录用户姓名
char id[128];         //当前登录用户id
int idtype;           //账户类型


// 查询结果集 MYSQL_RES 结构
//typedef struct MYSQL_RES {
//	uint64_t row_count;
//	MYSQL_FIELD *fields;
//	struct MYSQL_DATA *data;
//	MYSQL_ROWS *data_cursor;
//	unsigned long *lengths; /* column lengths of current row */
//	MYSQL *handle;          /* for unbuffered reads */
//	const struct MYSQL_METHODS *methods;
//	MYSQL_ROW row;         /* If unbuffered read */
//	MYSQL_ROW current_row; /* buffer to current row */
//	struct MEM_ROOT *field_alloc;
//	unsigned int field_count, current_field;
//	bool eof; /* Used by mysql_fetch_row */
//	/* mysql_stmt_close() had to cancel this result */
//	bool unbuffered_fetch_cancelled;
//	enum enum_resultset_metadata metadata;
//	void *extension;
//} MYSQL_RES;


void cls();//清屏
void ensureAndCls();//确定并清屏
void printLoginUI();//登录UI
void printRootMenu();//root菜单，0-16
void printTeacherMenu();//老师菜单，0-6
void printStudentMenu();//学生菜单，0-5
void serveRoot();//服务root
void serveTeacher();//服务老师
void serveStudent();//服务学生
void printStudentInfo(char* info);//打印所有学生信息，info用作标识，"all"标识所有学生信息，"tea"标识目前登录老师的学生
void printDomInfo();//打印所有寝室信息
void printClassInfo();//打印所有班级信息
void selectAndPrint(char* sql);//根据sql查表打印
void deleteStudent(char* info);//删除学生信息，info用作标识
void deleteDom();//删除宿舍信息
void deleteClass();//删除班级信息
void updateStudent(char* info);//修改学生信息，info用作标识
void updateDom();//修改寝室信息
void updateClass();//修改班级信息
char* getcid();//根据id找到cid（老师）
void addStudent(char* info);//添加学生信息
void addDom();//添加宿舍信息
void addClass();//添加班级信息
void changePwd();//学生修改密码
void printStuInsertLog();//查看学生表insert日志
void printMsg(char* info);//打印留言，info做标识
void deleteMsg();//删除留言
void addMsg();//添加留言

void login();//用户登录
int init();//初始化数据库
void service();//服务程序

int main() {
	service();
	return 0;
}

void cls() {
	system("cls");
}

void ensureAndCls() {
	system("pause");
	system("cls");
}

void printLoginUI() {
	printf("\n\t==========欢迎您==========\n");
	printf("\n==================================================\n");
	printf("\n\t\t1.登录\n");
	printf("\n\t\t2.退出\n");
	printf("\n==================================================\n");
	printf("\n\n\t\t请选择(1-2):");
}

void printRootMenu() {
	char post[20];
	strcpy(post, "管理员");
	printf("\n\t========欢迎您，%s(%s)%s==========\n", realName, id, post);
	printf("\n==================================================\n");
	printf("\n\t  学生管理");
	printf("\n\t\t 1.查看所有学生信息");
	printf("\n\t\t 2.删除某个学生信息");
	printf("\n\t\t 3.修改某个学生信息");
	printf("\n\t\t 4.添加一个学生信息");
	printf("\n\t  宿舍管理");
	printf("\n\t\t 5.查看所有宿舍信息");	/* 编号、楼号、寝室号、人数 */
	printf("\n\t\t 6.修改某个宿舍信息");
	printf("\n\t\t 7.删除某个宿舍信息");
	printf("\n\t\t 8.增加一个宿舍信息");
	printf("\n\t  班级管理");
	printf("\n\t\t 9.查看所有班级信息");	/* 名字、人数、年级、班主任、学历、编号 */
	printf("\n\t\t10.删除某个班级信息");
	printf("\n\t\t11.修改某个班级信息");
	printf("\n\t\t12.添加一个班级信息");
	printf("\n\t  其他");
	printf("\n\t\t13.查看所有留言");
	printf("\n\t\t14.删除某个留言");
	printf("\n\t\t15.查看学生表insert日志");	/* 需要从root/teacher添加一个学生 */
	printf("\n\t\t16.退出\n");
	printf("\n==================================================\n");
	printf("\n\t\t请选择(1-16):");

}

void printTeacherMenu() {
	char post[20];
	strcpy(post, "老师");
	printf("\n\t========欢迎您，%s(%s)%s==========\n", realName, id, post);
	printf("\n==================================================\n");
	printf("\n\t  学生管理\n");
	printf("\n\t\t1.查看所有学生信息\n");
	printf("\n\t\t2.删除某个学生信息\n");
	printf("\n\t\t3.修改某个学生信息\n");
	printf("\n\t\t4.添加一个学生信息\n");
	printf("\n\t  其他\n");
	printf("\n\t\t5.查看学生留言\n");
	printf("\n\t\t6.退出\n");
	printf("\n==================================================\n");
	printf("\n\n\t\t请选择(1-6):");

}

void printStudentMenu() {
	printf("\n\t========欢迎您，%s(%s)同学==========\n", realName, id);
	printf("\n==================================================\n");
	printf("\n\t\t1.查看个人信息\n");
	printf("\n\t\t2.留言\n");
	printf("\n\t\t3.修改密码\n");
	printf("\n\t\t4.退出\n");
	printf("\n==================================================\n");
	printf("\n\n\t\t请选择(1-4):");

}

void selectAndPrint(char* sql) {
	mysql_query(&mysql, sql);
	result = mysql_store_result(&mysql);
	printf("\n\n");
	for (int i = 0; i < result->field_count; i++)	// NULL
	{
		printf(" %-10s ", result->fields[i].name);
	}
	printf("\n\n");
	while (row = mysql_fetch_row(result)) {
		for (int i = 0; i < result->field_count; i++)
		{
			printf(" %-10s ", row[i]);
		}
		printf("\n\n");
	}
	mysql_free_result(result);
	printf("\n\n\n");
}

void printClassInfo() {
	char* sql = "select c.cid 班级编号,c.cname 班级,tea.name 老师,ifnull(t.nu,0) 人数,c.grade 年级,c.xueli 学历 from cls c left join (select cid,count(cid) nu from student group by cid) t on c.cid=t.cid join teacher tea on tea.tid=c.tid;";
	selectAndPrint(sql);
	ensureAndCls();
}

void printDomInfo() {	/* 宿舍号、楼号、寝室号、人数 */
	char* sql = "select d.did 宿舍号,t.nu 人数,d.lh 楼号,d.qsh 寝室号 from dom d join (select d.did,count(s.sid) nu from dom d left join student s on d.did=s.did group by d.did order by d.did) t on d.did=t.did order by d.did;";
	selectAndPrint(sql);
	ensureAndCls();

}

void printStudentInfo(char* info) {

	if (strcmp(info, "all") == 0) {	/* 打印所有学生的信息 */
		char* sql = "select s.sid 学号,s.sname 姓名,s.passwd 密码,s.sex 性别,s.did 宿舍号,d.lh 楼号,d.qsh 寝室号,c.cname 专业,c.grade 年级,c.xueli 学历,t.name 老师 from student s join	dom d on d.did = s.did join cls c on c.cid = s.cid join	teacher t on t.tid = c.tid order by s.sid; ";
		selectAndPrint(sql);

	}
	else if (strcmp(info, "tea") == 0) {
		char sql[256];
		sprintf(sql, "select s.sid 学号,s.sname 姓名,s.passwd 密码,d.did 宿舍号,d.lh 楼号,d.qsh 寝室号,c.cname 班级,c.grade 级别,c.xueli 学历,c.tid 老师 from student s join dom d on d.did=s.did join cls c on s.cid=c.cid and c.tid='%s';", id);
		//printf("sql:%s\n", sql);
		selectAndPrint(sql);
	}
	else if (strcmp(info, "stu") == 0) {
		char sql[256];
		sprintf(sql, "select s.sid 学号,s.sname 姓名,s.passwd 密码,d.did 宿舍号,d.lh 楼号,d.qsh 寝室号,c.cname 班级,c.grade 级别,c.xueli 学历,c.tid 老师 from student s join dom d on d.did=s.did join cls c on s.cid=c.cid and s.sid=%s;", id);
		//printf("sql:%s\n", sql);
		selectAndPrint(sql);
	}

	ensureAndCls();
}

void deleteStudent(char* info) {

	char sid[20];
	printf("\n\n请输入待删除学生编号：");
	scanf("%s", sid);
	while (getchar() != '\n');

	cls();

	// 若 info=tea，则表示当前登录用户是老师，则只能删除自己的学生

	if (strcmp(info, "tea") == 0) {
		char s[2560];
		sprintf(s, "select s.*,c.* from student s join cls c on s.cid=c.cid and c.tid='%s' and s.sid=%s;", id, sid);

		printf("\nsql: %s\n\n", s);

		mysql_query(&mysql, s);
		result = mysql_store_result(&mysql);
		int num = result->row_count;

		//printf("sql:%s\nnum:%d\n", s, num);

		if (num != 1) {
			printf("\n\n删除失败!\n\n");	/* 学号不存在 */
			mysql_free_result(result);
			ensureAndCls();
			return;
		}

		mysql_free_result(result);
	}

	// 学生删除逻辑

	char sql[512];
	sprintf(sql, "delete from student where sid=%s;", sid);

	printf("\nsql: %s\n\n", sql);

	int rst = mysql_real_query(&mysql, sql, strlen(sql));
	/*
		rst!=0  -> 错误
		rst==0  -> 语句正确，可能成功可能失败，用 mysql_affected_rows() 判断
	*/
	int affect = mysql_affected_rows(&mysql);

	//printf("rst:%d, affect:%d\n", rst, affect);

	if (0 == rst && affect == 1) {
		printf("\n删除成功!\n\n");
	}
	else {
		printf("\n删除失败!（请检查留言表是否使用外键引用了该学生信息）\n\n");		/* sid做 msg 表的外键时不可删 */
	}

	ensureAndCls();
}

void deleteDom() {
	char did[20];
	printf("\n\n请输入待删除宿舍编号：");
	scanf("%s", did);
	while (getchar() != '\n');

	cls();

	char sql[512];
	sprintf(sql, "delete from dom where did=%s;", did);
	int rst = mysql_real_query(&mysql, sql, strlen(sql));
	/*
		rst!=0  -> 错误
		rst==0  -> 语句正确，可能成功可能失败，用 mysql_affected_rows() 判断
	*/
	if (!rst && mysql_affected_rows(&mysql)) {
		printf("\n删除成功！\n\n");
	}
	else {
		printf("\n删除失败\n\n");
	}

	ensureAndCls();
}

void deleteClass() {
	char cid[20];
	printf("\n\n请输入待删除班级编号：");
	scanf("%s", cid);
	while (getchar() != '\n');

	cls();

	char sql[512];
	sprintf(sql, "delete from cls where cid=%s;", cid);
	int rst = mysql_real_query(&mysql, sql, strlen(sql));
	/*
		rst!=0  -> 错误
		rst==0  -> 语句正确，可能成功可能失败，用 mysql_affected_rows() 判断
	*/
	if (!rst && mysql_affected_rows(&mysql)) {
		printf("\n删除成功！\n\n");
	}
	else {
		printf("\n删除失败\n\n");
	}

	ensureAndCls();
}

void updateStudent(char* info) {

	char sid[20];
	printf("\n\n请输入待修改学生编号：");
	scanf("%s", sid);
	while (getchar() != '\n');

	cls();

	// 老师删除时只能修改自己的学生

	if (strcmp(info, "tea") == 0) {
		char s[2560];
		sprintf(s, "select s.*,c.* from student s join cls c on s.cid=c.cid and c.tid='%s' and s.sid=%s;", id, sid);

		printf("\nsql: %s\n\n", s);

		mysql_query(&mysql, s);
		result = mysql_store_result(&mysql);
		int num = result->row_count;

		//printf("sql:%s\nnum:%d\n", s, num);

		if (num != 1) {
			printf("\n\n该学生不存在，修改失败!\n\n");
			mysql_free_result(result);
			ensureAndCls();
			return;
		}

		mysql_free_result(result);
	}

	//1.查出信息 sid、sname、passwd、sex、cid、did

	char sql[512];
	sprintf(sql, "select * from student where sid=%s;", sid);
	printf("sql: %s\n\n", sql);
	mysql_query(&mysql, sql);
	result = mysql_store_result(&mysql);
	//printf("sql:%s, rst:%d\n", sql, result->row_count);
	char ks[10][256], vs[10][256], os[10][256];	//一个表最多10列
	int nu;
	if (result->row_count == 1)
	{
		row = mysql_fetch_row(result);
		int num = result->field_count;
		nu = num;

		for (int i = 0; i < num; i++)	/* 存放列名 */
		{
			strcpy(ks[i], result->fields[i].name);
		}
		for (int i = 0; i < num; i++)
		{
			strcpy(vs[i], row[i]);
		}

		//2.交互修改

		printf("\n进行修改:");
		for (int i = 0; i < num; i++)
		{
			if (strcmp("sid", ks[i]) == 0) {
				printf("\n\n\t%s = %s\n", ks[i], vs[i]);
				strcpy(os[i], vs[i]);
				continue;	// 主键不能修改
			}

			printf("\n\t%s = %s, 改为: ", ks[i], vs[i]);
			scanf("%s", os[i]);
			while (getchar() != '\n');

		}

	}
	else {
		printf("\n\n学生不存在，修改失败!\n\n");	/* 不存在 */
		mysql_free_result(result);
		ensureAndCls();
		return;
	}
	mysql_free_result(result);

	//3.update

	char s[512];
	strcpy(s, "update student set ");
	for (int i = 1; i < nu; i++)
	{
		char buf[128];
		if (strcmp("passwd", ks[i]) == 0 || strcmp("sname", ks[i]) == 0 || strcmp("sex", ks[i]) == 0)
		{
			sprintf(buf, "%s='%s'", ks[i], os[i]);	// string
		}
		else {
			sprintf(buf, "%s=%s", ks[i], os[i]);	// int
		}
		strcat(s, buf);
		if (i == nu - 1)
		{
			strcat(s, " ");
		}
		else {
			strcat(s, ",");
		}
	}
	char buf[128];
	sprintf(buf, "where %s=%s;", ks[0], os[0]);
	strcat(s, buf);

	//printf("update:  %s\n", s);

	int rst = mysql_real_query(&mysql, s, strlen(s));
	/*
		rst!=0  -> 错误
		rst==0  -> 语句正确，可能成功可能失败，用 mysql_affected_rows() 判断
	*/
	if (!rst && mysql_affected_rows(&mysql)) {
		printf("\n学生信息修改成功！\n\n");
	}
	else {
		printf("\n学生信息修改失败\n\n");	/* 约束、 */
	}

	ensureAndCls();

}

void updateDom() {

	char did[20];
	printf("\n\n请输入待修改宿舍编号：");
	scanf("%s", did);
	while (getchar() != '\n');

	cls();

	//1.查出信息 did、lh、qsh

	char sql[512];
	sprintf(sql, "select * from dom where did=%s;", did);
	mysql_query(&mysql, sql);
	result = mysql_store_result(&mysql);
	//printf("sql:%s, rst:%d\n", sql, result->row_count);
	char ks[10][256], vs[10][256], os[10][256];	//一个表最多10列
	int nu;
	if (result->row_count == 1)
	{
		row = mysql_fetch_row(result);
		int num = result->field_count;
		nu = num;

		for (int i = 0; i < num; i++)	/* 存放列名 */
		{
			strcpy(ks[i], result->fields[i].name);
		}
		for (int i = 0; i < num; i++)
		{
			strcpy(vs[i], row[i]);
		}

		//2.交互修改

		printf("\n进行修改:");
		for (int i = 0; i < num; i++)
		{
			if (strcmp("did", ks[i]) == 0) {
				printf("\n\n\t%s = %s\n", ks[i], vs[i]);
				strcpy(os[i], vs[i]);
				continue;	// 主键不能修改
			}

			printf("\n\t%s = %s, 改为: ", ks[i], vs[i]);
			scanf("%s", os[i]);
			while (getchar() != '\n');

		}

	}
	else {
		printf("\n\n该宿舍不存在，修改失败!\n\n");	/* 不存在 */
		mysql_free_result(result);
		ensureAndCls();
		return;
	}
	mysql_free_result(result);

	//3.update

	char s[512];
	strcpy(s, "update dom set ");
	for (int i = 1; i < nu; i++)
	{
		char buf[128];
		sprintf(buf, "%s=%s", ks[i], os[i]);	// int
		strcat(s, buf);
		if (i == nu - 1)
		{
			strcat(s, " ");
		}
		else {
			strcat(s, ",");
		}
	}
	char buf[128];
	sprintf(buf, "where %s=%s;", ks[0], os[0]);
	strcat(s, buf);

	//printf("update:  %s\n", s);

	int rst = mysql_real_query(&mysql, s, strlen(s));
	/*
		rst!=0  -> 错误
		rst==0  -> 语句正确，可能成功可能失败，用 mysql_affected_rows() 判断
	*/
	if (!rst && mysql_affected_rows(&mysql)) {
		printf("\n宿舍信息修改成功！\n\n");
	}
	else {
		printf("\n宿舍信息修改失败\n\n");	/* 约束、 */
	}

	ensureAndCls();

}

void updateClass() {

	char cid[20];
	printf("\n\n请输入待修改班级编号：");
	scanf("%s", cid);
	while (getchar() != '\n');

	cls();

	//1.查出信息 cid、cname、grade、xueli、tid

	char sql[512];
	sprintf(sql, "select * from cls where cid=%s;", cid);
	mysql_query(&mysql, sql);
	result = mysql_store_result(&mysql);
	//printf("sql:%s, rst:%d\n", sql, result->row_count);
	char ks[10][256], vs[10][256], os[10][256];	//一个表最多10列
	int nu;
	if (result->row_count == 1)
	{
		row = mysql_fetch_row(result);
		int num = result->field_count;
		nu = num;

		for (int i = 0; i < num; i++)	/* 存放列名 */
		{
			strcpy(ks[i], result->fields[i].name);
		}
		for (int i = 0; i < num; i++)
		{
			strcpy(vs[i], row[i]);
		}

		//2.交互修改

		printf("\n进行修改:");
		for (int i = 0; i < num; i++)
		{
			if (strcmp("cid", ks[i]) == 0) {
				printf("\n\n\t%s = %s\n", ks[i], vs[i]);
				strcpy(os[i], vs[i]);
				continue;	// 主键不能修改
			}

			printf("\n\t%s = %s, 改为: ", ks[i], vs[i]);
			scanf("%s", os[i]);
			while (getchar() != '\n');

		}

	}
	else {
		printf("\n\n该班级不存在，修改失败!\n\n");	/* 不存在 */
		mysql_free_result(result);
		ensureAndCls();
		return;
	}
	mysql_free_result(result);

	//3.update

	char s[512];
	strcpy(s, "update cls set ");
	for (int i = 1; i < nu; i++)
	{
		char buf[128];
		sprintf(buf, "%s='%s'", ks[i], os[i]);	// string
		strcat(s, buf);
		if (i == nu - 1)
		{
			strcat(s, " ");
		}
		else {
			strcat(s, ",");
		}
	}
	char buf[128];
	sprintf(buf, "where %s=%s;", ks[0], os[0]);
	strcat(s, buf);

	//printf("update:  %s\n", s);

	int rst = mysql_real_query(&mysql, s, strlen(s));
	/*
		rst!=0  -> 错误
		rst==0  -> 语句正确，可能成功可能失败，用 mysql_affected_rows() 判断
	*/
	if (!rst && mysql_affected_rows(&mysql)) {
		printf("\n班级信息修改成功！\n\n");
	}
	else {
		printf("\n班级信息修改失败\n\n");	/* 约束、 */
	}

	ensureAndCls();
}

/// <summary>
/// 获取老师(id)所在的班级id(cid)
/// </summary>
/// <returns></returns>
char* getcid() {
	char sql[256], rst[256];
	sprintf(sql, "select cid,tid from cls where tid='%s';", id);
	mysql_query(&mysql, sql);
	result = mysql_store_result(&mysql);
	if (result->row_count >= 1)
	{
		row = mysql_fetch_row(result);
		strcpy(rst, row[0]);
	}
	else {
		strcpy(rst, "");
	}
	mysql_free_result(result);
	return rst;
}

/// <summary>
/// 添加学生信息
/// </summary>
/// <param name="info">添加学生的时机，info=tea 时表示添加学生到当前教师所在的班级</param>
void addStudent(char* info) {

	char ks[10][256] = { "sid", "sname", "passwd", "sex", "cid", "did" };//学生表各字段
	char vs[10][256];
	int nu = 6;//列数

	printf("\n进行添加:");
	int index_cid = -1;//记录cid列
	for (int i = 0; i < nu; i++)
	{
		if (strcmp("cid", ks[i]) == 0) {	/* 班级最后指定 */
			index_cid = i;
			continue;	// 主键不能修改
		}

		printf("\n\t%s =  ", ks[i]);
		scanf("%s", vs[i]);
		while (getchar() != '\n');
	}

	//printf("%s\n", info);

	if (strcmp(info, "tea") == 0) {
		strcpy(vs[index_cid], getcid());
	}
	else if (strcmp(info, "all") == 0) {
		printf("\n\tcid = ");
		scanf("%s", vs[index_cid]);
		while (getchar() != '\n');
	}

	/*for (int i = 0; i < nu; i++)
	{
		printf("%s = %s\n", ks[i], vs[i]);
	}*/

	cls();

	// 拼接sql
	char sql[1024] = "";
	strcat(sql, "insert into student");
	for (int i = 0; i < nu; i++)
	{
		char s[128];

		if (i == 0)
		{
			sprintf(s, "(%s,", ks[i]);
		}
		else if (i == nu - 1)
		{
			sprintf(s, "%s) ", ks[i]);
		}
		else {
			sprintf(s, "%s,", ks[i]);
		}

		strcat(sql, s);
	}

	strcat(sql, "values");
	for (int i = 0; i < nu; i++)
	{
		char s[128];

		if (i == 0)
		{
			sprintf(s, "(%s,", vs[i]);
		}
		else if (i == nu - 1)
		{
			sprintf(s, "%s);", vs[i]);
		}
		else if (i == nu - 2)
		{
			sprintf(s, "%s,", vs[i]);
		}
		else {
			sprintf(s, "'%s',", vs[i]);
		}

		strcat(sql, s);
	}

	/*printf("sql:%s\n", sql);
	ensureAndCls();*/

	// 插入
	int rst = mysql_real_query(&mysql, sql, strlen(sql));
	printf("\nsql: %s\n\n", sql);
	/*
		rst!=0  -> 错误
		rst==0  -> 语句正确，可能成功可能失败，用 mysql_affected_rows() 判断
	*/
	if (!rst && mysql_affected_rows(&mysql)) {
		printf("\n添加学生信息成功！\n\n");
	}
	else {
		printf("\n添加学生信息失败\n\n");	/* 约束、 */
	}

	ensureAndCls();
}
void addDom() {
	char ks[10][256] = { "did","lh","qsh" };//宿舍表各字段
	char vs[10][256];
	int nu = 3;

	printf("\n进行添加:");
	for (int i = 0; i < nu; i++)
	{
		printf("\n\t%s =  ", ks[i]);
		scanf("%s", vs[i]);
		while (getchar() != '\n');

	}
	// 拼接sql
	char sql[1024] = "";
	strcat(sql, "insert into dom");
	for (int i = 0; i < nu; i++)
	{
		char s[128];

		if (i == 0)
		{
			sprintf(s, "(%s,", ks[i]);
		}
		else if (i == nu - 1)
		{
			sprintf(s, "%s) ", ks[i]);
		}
		else {
			sprintf(s, "%s,", ks[i]);
		}

		strcat(sql, s);
	}

	strcat(sql, "values");
	for (int i = 0; i < nu; i++)
	{
		char s[128];

		if (i == 0)
		{
			sprintf(s, "(%s,", vs[i]);
		}
		else if (i == nu - 1)
		{
			sprintf(s, "%s);", vs[i]);
		}
		else {
			sprintf(s, "%s,", vs[i]);
		}

		strcat(sql, s);
	}

	//printf("sql:%s\n", sql);

	// 插入
	int rst = mysql_real_query(&mysql, sql, strlen(sql));
	/*
		rst!=0  -> 错误
		rst==0  -> 语句正确，可能成功可能失败，用 mysql_affected_rows() 判断
	*/
	if (!rst && mysql_affected_rows(&mysql)) {
		printf("\n添加宿舍信息成功！\n\n");
	}
	else {
		printf("\n添加宿舍信息失败\n\n");	/* 约束、 */
	}

	ensureAndCls();

}
void addClass() {
	char ks[10][256] = { "cid","cname","grade","xueli","tid" };//宿舍表各字段
	char vs[10][256];
	int nu = 5;

	printf("\n进行添加:");
	for (int i = 0; i < nu; i++)
	{
		printf("\n\t%s =  ", ks[i]);
		scanf("%s", vs[i]);
		while (getchar() != '\n');

	}
	// 拼接sql
	char sql[1024] = "";
	strcat(sql, "insert into cls");
	for (int i = 0; i < nu; i++)
	{
		char s[128];

		if (i == 0)
		{
			sprintf(s, "(%s,", ks[i]);
		}
		else if (i == nu - 1)
		{
			sprintf(s, "%s) ", ks[i]);
		}
		else {
			sprintf(s, "%s,", ks[i]);
		}

		strcat(sql, s);
	}

	strcat(sql, "values");
	for (int i = 0; i < nu; i++)
	{
		char s[128];

		if (i == 0)
		{
			sprintf(s, "(%s,", vs[i]);
		}
		else if (i == nu - 1)
		{
			sprintf(s, "'%s');", vs[i]);
		}
		else {
			sprintf(s, "'%s',", vs[i]);
		}

		strcat(sql, s);
	}

	/*printf("sql:%s\n", sql);
	return;*/

	// 插入
	int rst = mysql_real_query(&mysql, sql, strlen(sql));
	/*
		rst!=0  -> 错误
		rst==0  -> 语句正确，可能成功可能失败，用 mysql_affected_rows() 判断
	*/
	if (!rst && mysql_affected_rows(&mysql)) {
		printf("\n添加班级信息成功！\n\n");
	}
	else {
		printf("\n添加班级信息失败\n\n");	/* 约束、 */
	}

	ensureAndCls();
}

void printMsg(char* info) {

	char sql[1024];
	if (strcmp(info, "all") == 0)
	{
		strcpy(sql, "select m.mid,t.sid,t.sname,m.txt from msg m join (select s.sid,s.sname,t.tid from teacher t join cls c on t.tid=c.tid join student s on s.cid=c.cid) t on m.sid=t.sid order by	m.mid;");
	}
	else if (strcmp(info, "tea") == 0) {
		sprintf(sql, "select m.mid,t.sid,t.sname,m.txt from msg m join (select s.sid,s.sname,t.tid from teacher t join cls c on t.tid=c.tid join student s on s.cid=c.cid) t on m.sid=t.sid and t.tid='%s' order by	m.mid;", id);
	}

	cls();

	selectAndPrint(sql);

	ensureAndCls();

}
void deleteMsg() {
	char str[256];
	printf("\n请输入待删除的留言编号：");
	scanf("%s", str);
	while (getchar() != '\n');

	cls();

	char sql[512];
	sprintf(sql, "delete from msg where mid=%s;", str);

	// 插入
	int rst = mysql_real_query(&mysql, sql, strlen(sql));
	/*
		rst!=0  -> 错误
		rst==0  -> 语句正确，可能成功可能失败，用 mysql_affected_rows() 判断
	*/
	if (!rst && mysql_affected_rows(&mysql)) {
		printf("\n删除留言成功！\n\n");
	}
	else {
		printf("\n删除留言失败\n\n");	/* 约束、 */
	}

	ensureAndCls();
}

void addMsg() {
	char str[256];
	printf("\n请留言：");
	scanf("%s", str);
	while (getchar() != '\n');

	cls();

	char sql[512];
	sprintf(sql, "insert into msg(sid,txt) values('%s','%s');", id, str);

	// 插入
	int rst = mysql_real_query(&mysql, sql, strlen(sql));
	/*
		rst!=0  -> 错误
		rst==0  -> 语句正确，可能成功可能失败，用 mysql_affected_rows() 判断
	*/
	if (!rst && mysql_affected_rows(&mysql)) {
		printf("\n留言成功！\n\n");
	}
	else {
		printf("\n留言失败\n\n");	/* 约束、 */
	}

	ensureAndCls();
}

void changePwd() {

	char str[256];
	printf("\n请输入新密码：");
	scanf("%s", str);
	while (getchar() != '\n');

	cls();

	char sql[512];
	sprintf(sql, "update student set passwd='%s' where sid=%s;", str, id);

	// 插入
	int rst = mysql_real_query(&mysql, sql, strlen(sql));
	/*
		rst!=0  -> 错误
		rst==0  -> 语句正确，可能成功可能失败，用 mysql_affected_rows() 判断
	*/
	if (!rst && mysql_affected_rows(&mysql)) {
		printf("\n密码修改成功！\n\n");
	}
	else {
		printf("\n密码修改失败\n\n");	/* 约束、 */
	}

	ensureAndCls();

}

void printStuInsertLog() {

	char* sql = "select id 编号,op_time 操作时间,content 内容 from student_insert_log;";

	selectAndPrint(sql);

	ensureAndCls();

}

/* 服务管理员 */
void serveRoot() {

	while (true)
	{
		printRootMenu();
		char buf[10];
		scanf("%s", buf);
		while (getchar() != '\n'); /* flush */
		cls();

		int num = atoi(buf);
		if (num != 0 && num > 0 && num < 17)	/* 1-16 */
		{
			if (num == 16) break;
			switch (num)
			{
			case 1:
				printStudentInfo("all");
				break;
			case 2:
				deleteStudent("all");
				break;
			case 3:
				updateStudent("all");
				break;
			case 4:
				addStudent("all");	/* 指定cid */
				break;
			case 5:
				printDomInfo();
				break;
			case 6:
				updateDom();
				break;
			case 7:
				deleteDom();
				break;
			case 8:
				addDom();
				break;
			case 9:
				printClassInfo();
				break;
			case 10:
				deleteClass();
				break;
			case 11:
				updateClass();
				break;
			case 12:
				addClass();
				break;
			case 13:
				printMsg("all");
				break;
			case 14:
				deleteMsg();
				break;
			case 15:
				printStuInsertLog();
				break;

			default:
				break;
			}

		}
	}

	cls();

}

/* 服务老师 */
void serveTeacher() {

	while (true)
	{
		printTeacherMenu();
		char buf[10];
		scanf("%s", buf);
		while (getchar() != '\n'); /* flush */
		cls();

		int num = atoi(buf);
		if (num != 0 && num > 0 && num < 7)	/* 1-6 */
		{
			if (num == 6) break;

			switch (num)
			{
			case 1:
				printStudentInfo("tea");	/* 查看本班学生信息 */
				break;
			case 2:
				deleteStudent("tea");	/* 只能删除自己的学生信息 */
				break;
			case 3:
				updateStudent("tea");	/* 修改本班学生信息 */
				break;
			case 4:
				addStudent("tea");	/* 老师添加学生只能进自己班 */
				break;
			case 5:
				printMsg("tea");	/* 查看本班学生留言 */
				break;

			default:
				break;
			}

		}
	}

	cls();

}

/* 服务学生 */
void serveStudent() {

	while (true)
	{
		printStudentMenu();
		char buf[10];
		scanf("%s", buf);
		while (getchar() != '\n'); /* flush */
		cls();

		int num = atoi(buf);
		if (num != 0 && num > 0 && num < 5)	/* 1-4 */
		{
			if (num == 4) break;

			switch (num)
			{
			case 1:
				printStudentInfo("stu");
				break;
			case 2:
				addMsg();
				break;
			case 3:
				changePwd();
				break;

			default:
				break;
			}

		}
	}

	cls();

}

void login() {
	char name[128];
	char pwd[128];
	idtype = 0;
	for (int i = 0; i < 128; i++) {
		name[i] = '\0';
		id[i] = '\0';
		pwd[i] = '\0';
		realName[i] = '\0';
	}
	printf("\nlogin:\n");
	printf("请输入账号:");
	scanf("%s", name);
	while (getchar() != '\n');
	printf("请输入密码:");
	scanf("%s", pwd);
	while (getchar() != '\n');

	// teacher 1 root      name -> varchar
	// teacher 2 teacher   name -> varchar
	// student /   /       name -> int

	int userId = atoi(name);
	int goToMenu = 0;

	if (userId == 0)	/* root/teacher */
	{
		// 查表
		char buf[512];
		sprintf(buf, "select * from teacher where tid='%s' and passwd='%s';", name, pwd);
		/* select * from teacher where tid='root' and passwd='root'; */
		mysql_query(&mysql, buf);
		result = mysql_store_result(&mysql);

		//unsigned colNum = result->field_count;//结果集列数
		///*unsigned long long*/int rowNum = result->row_count;//结果集行数
		//printf("列数：%u\n", colNum);
		//printf("行数：%d\n", rowNum);
		//for (int i = 0; i < result->field_count; i++)
		//	printf("第%d列: %s\n", i, result->fields[i].name);
		///*列数：5
		//	行数：1
		//	第0列 : tid
		//	第1列 : passwd
		//	第2列 : name
		//	第3列 : sex
		//	第4列 : idtype*/

		if (result->row_count == 1)	/* 登录成功 */
		{
			int index_name = -1;// 记录真实名字列索引
			int index_type = -1;// 记录身份类型列索引
			for (int i = 0; i < result->field_count; i++)
			{
				if (strncmp("name", result->fields[i].name, 5) == 0) {
					index_name = i;
				}
				if (strncmp("idtype", result->fields[i].name, 7) == 0) {
					index_type = i;
				}
			}
			if (index_name != -1 && index_type != -1) {
				row = mysql_fetch_row(result);
				strncpy(realName, row[index_name], strlen(row[index_name]));
				strncpy(id, name, strlen(name));
				idtype = atoi(row[index_type]);

				//printf("id:%s, name:%s\n", id, realName);

				goToMenu = 1;
			}

		}

		mysql_free_result(result); //释放结果集
	}
	else {	/* student */
		char buf[512];
		sprintf(buf, "select * from student where sid=%s and passwd='%s';", name, pwd);
		/* select * from student where sid = 777 and passwd = '777'; */
		mysql_query(&mysql, buf);
		result = mysql_store_result(&mysql);
		if (result->row_count == 1)	/* 登录成功 */
		{
			int index = -1;// 记录真实名字列索引
			for (int i = 0; i < result->field_count; i++)
			{
				if (strncmp("sname", result->fields[i].name, 5) == 0) {
					index = i;
					break;
				}
			}
			if (index != -1) {
				row = mysql_fetch_row(result);
				strncpy(realName, row[index], strlen(row[index]));
				strncpy(id, name, strlen(name));

				//printf("id:%s, name:%s\n", id, realName);
				goToMenu = 1;
			}

		}

		mysql_free_result(result); //释放结果集

	}

	cls();

	if (goToMenu) {
		if (idtype == 1) {	/* 1root */
			serveRoot();
		}
		else if (idtype == 2) {	/* 2teacher */
			serveTeacher();
		}
		else {
			serveStudent();
		}
	}
	else {
		printf("登录失败，密码或账号错误！\n\n");
		ensureAndCls();
	}

}

int init() {
	mysql_init(&mysql);//初始化数据库
	mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "gbk");//设置编码方式
	//连接数据库
	if (!mysql_real_connect(&mysql, "localhost", "root", "mysql", "sms", 3306, NULL, 0)) {
		printf("\n数据库连接失败！请确定参数信息是否正确。。\n");
		ensureAndCls();
		return -1;
	}
	return 1;
}

void service() {
	if (init() == -1) {	/* 初始化连接 */
		return;
	}
	while (1) {
		printLoginUI();
		char buf[10];
		scanf("%s", buf);
		while (getchar() != '\n'); /* flush */
		cls();

		int num = atoi(buf);
		if (num != 0 && num > 0 && num < 3)	/* 1登录，2退出 */
		{
			if (num == 2) {
				mysql_close(&mysql);          //释放连接	
				break;
			}
			login();
		}
	}

}

